﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for BillDao
/// </summary>
public class BillDao
{
    #region "variable"    
    private string _error;
    private MyConnection _myConn;
    #endregion


    #region "properties"
    public string Error
    {
        get { return _error; }
        set { _error = value; }
    }

    #endregion
	public BillDao()
	{        
        _error = "";
        _myConn = new MyConnection();
	}
    public int SelectCountBillById_SD(int id_SD)
    {
        string spName = "sp_Select_CountBillById_SD";
        SqlParameter[] parameter = { new SqlParameter("@id_SD", SqlDbType.Int) };
        parameter[0].Value = id_SD;
        return (int)_myConn.GetValue(spName, parameter);
    }
    public List<BillDto> SelectAllBills()
    {
        string spName = "sp_Select_AllBills";
        DataTable table = _myConn.GetDataTable(spName, 1);
        return MakeListBillDto(table);
    }
    public List<BillDto> SelectBillsByService(string service)
    {
        string spName = "sp_Select_BillsByService";
        SqlParameter[] parameter = {new SqlParameter("@serviceName",SqlDbType.NVarChar,30)};
        parameter[0].Value = service;
        DataTable table = _myConn.GetDataTable(spName, parameter);
        return MakeListBillDto(table);  
    }
    public void InsertBill(BillDto bill)
    {
        string spName = "sp_Insert_Bill";
        SqlParameter[] parameter = { new SqlParameter("@id_Bill", SqlDbType.Int),
                                     new SqlParameter("@id_MN", SqlDbType.Int),
                                     new SqlParameter("@id_SD", SqlDbType.Int),
                                     new SqlParameter("@_createDate", SqlDbType.DateTime),                                     
                                     new SqlParameter("@_billStatus", SqlDbType.Bit)};
        parameter[0].Direction = ParameterDirection.Output;
        parameter[1].Value = bill.Id_MN;
        parameter[2].Value = bill.Id_SD;
        parameter[3].Value = bill.CreateDate;        
        parameter[4].Value = bill.BillStatus;
        int newID = _myConn.ExecuteNonQuery(spName, parameter);
        bill.Id_Bill = newID;
    }
    public void UpdatePaidBill(int id_Bill)
    {
        string spName = "sp_Update_PaidBill";
        DateTime paymentDate=DateTime.Now;
        SqlParameter[] paramater = { new SqlParameter("@id_Bill",SqlDbType.Int),
                                     new SqlParameter("@_paymentDate",SqlDbType.DateTime)};        
        paramater[0].Value = id_Bill;
        paramater[1].Value = paymentDate;
        _myConn.ExecuteNonQuery(spName,paramater);
    }
    public List<BillDto> SelectBillAdv(int id_MN,int id_ST,bool billStatus,string orderby,string sort,string search_createDate, string search_paymentDate)
    {
        string spName = "sp_Select_BillAdv";
        SqlParameter[] parameter = {   new SqlParameter("@id_MN", SqlDbType.Int),
                                       new SqlParameter("@id_ST",SqlDbType.Int), 
                                       new SqlParameter("@_billStatus",SqlDbType.Bit),
                                       new SqlParameter("@_order",SqlDbType.NVarChar,20),
                                       new SqlParameter("@_desc",SqlDbType.NVarChar,10),
                                       new SqlParameter("@_createDate",SqlDbType.NVarChar,20),
                                       new SqlParameter("@_paymentDate",SqlDbType.NVarChar,20)};
        parameter[0].Value = id_MN;
        parameter[1].Value = id_ST;
        parameter[2].Value = billStatus;
        parameter[3].Value = orderby;
        parameter[4].Value = sort;
        parameter[5].Value = search_createDate;
        parameter[6].Value = search_paymentDate;
        DataTable table = _myConn.GetDataTable(spName, parameter);
        return MakeListBillDto(table);
    }
    public List<BillDto> SelectBillOfOtherService(int id_MN, bool billStatus, string orderby, string sort, string search_createDate, string search_paymentDate)
    {
        string spName = "sp_Select_BillOfOtherService";
        SqlParameter[] parameter = {   new SqlParameter("@id_MN", SqlDbType.Int),                                       
                                       new SqlParameter("@_billStatus",SqlDbType.Bit),
                                       new SqlParameter("@_order",SqlDbType.NVarChar,20),
                                       new SqlParameter("@_desc",SqlDbType.NVarChar,10),
                                       new SqlParameter("@_createDate",SqlDbType.NVarChar,20),
                                       new SqlParameter("@_paymentDate",SqlDbType.NVarChar,20)};
        parameter[0].Value = id_MN;        
        parameter[1].Value = billStatus;
        parameter[2].Value = orderby;
        parameter[3].Value = sort;
        parameter[4].Value = search_createDate;
        parameter[5].Value = search_paymentDate;
        DataTable table = _myConn.GetDataTable(spName, parameter);
        return MakeListBillDto(table);
    }
    public List<BillDto> MakeListBillDto(DataTable table)
    {
        List<BillDto> list = new List<BillDto>();
        foreach (DataRow row in table.Rows)
        {
            list.Add(new BillDto(row));
        }
        return list;
    }
    
}